import json
import requests
import pymysql

#协同平台-船舶安全监督管理系统-国内安全检信息查询
BASE_URL = 'http://cmp.msa.gov.cn/supervision/shipfsc/handle.action?act=fscQuery&fscQO.inspector_name=&shipDataQO.ship_id=&shipDataQO.ship_name_cn=&shipDataQO.ship_firstreg_no=&shipDataQO.org_code=&shipDataQO.ship_type_code=&shipDataQO.built_date_start=&shipDataQO.built_date_end=&shipDataQO.ship_grosston_start=&shipDataQO.ship_grosston_end=&fscQO.inspect_date_start=2024-10-01&fscQO.inspect_date_end=2024-12-31&fscQO.org_code=140000&fscQO.org_limit=1&fscQO.inspect_type=&shipDataQO.regport_code=&shipDataQO.regport_limit=&fscQO.is_passed=&fscQO.detention_mark=&shipDataQO.ship_owner=&fscQO.correct_mark=&shipDataQO.ship_operator='
headers = {
    'Accept': 'application/json, text/javascript, */*; q=0.01',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/x-www-form-urlencoded; charset=UTF-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': '3V%2B4sNcb7ceJ6Na9d%2FX5Nq0BVUJzrNSbP4ShsVaw3u6%2FXbxJPvN7t4PAwkbKmT7yE3SWRG8NO3JGYp0%2BkyIno9MdVyZ8Zn62KFRUOOrAd3MysMlWadCC7cWuo%2BOqvgmchNcXFE9UWRz8L5K9cwd6iNBu9TNHiyQKeFnQshzq2Vi5ETsYPVBTYRrIHuOj9UhYXVv%2Bk5y9vzALLnyD07L8lDqbxV%2BGBLYWnavYVimzTaPKgffC4duqvGCsZEGgMdWkclILB%2Bh58Qepswc9P24cASSlsDk1a%2Bq8R4eVsBrlaonJETK8d%2FZEB5%2F8Lvu7cpnBRlPVzfl4cUNhKbAB45CyddV4g7ReQJCVh1%2BUuB5H%2F7fKGax4W5p4Cb1x4byuFvBjc9X5NxhfrAP%2Bjs%2BIueyAWXqS%2FBwqbQ1ZaJKz0q89mjZOyjJfFzD6oVOSYT0AtEU3uH6Z6jTHXDDa4D30%2FgUvE0hQHn1hQCQMC63gp12%2BNPI%3D',
    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': 'rd1WJpke6vd0COivAzO0lbPblJwLDfN0THN1807N7nb5OuMCBoAN!1701920017'
}

session = requests.Session()
# 数据库连接配置
db_config = {
    'host': '191.254.8.220',
    'user': 'root',
    'password': 'Szmsa@520',
    'db': 'spiders',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}
# 表名和字段名
table_name = 'xt_safety_ship_internal_security_32_35'
# 连接数据库
connection = pymysql.connect(**db_config)


def insertData(ship_region_type,inspect_mark):
    try:
        url =   BASE_URL+'&shipDataQO.ship_region_type='+ship_region_type+'&fscQO.initial_inspect_mark='+inspect_mark
        response_lo = session.post(url, headers=headers, cookies=cookies)
        print(response_lo.text)
        datas = json.loads(response_lo.text)
        if response_lo.status_code == 200:
            # 开始事务
            with connection.cursor() as cursor:
                for record in datas:
                    # 获取需要插入的字段名称
                    fields = ', '.join(record.keys())
                    # 拼装需要插入字段对应的%号个数
                    values = ", ".join(['%s'] * len(record))
                    inspect_no = record['inspect_no']

                    # value值是json的字段无法直接存入库，要转成string
                    json_inspect_date = record['inspect_date']
                    json_create_time = record['create_time']
                    json_operate_time = record['operate_time']

                    # json对象转成string后重新赋值回原来的字段
                    str_inspect_date = json.dumps(json_inspect_date)
                    str_create_time = json.dumps(json_create_time)
                    str_operate_time = json.dumps(json_operate_time)

                    record['inspect_date'] = str_inspect_date
                    record['create_time'] = str_create_time
                    record['operate_time'] = str_operate_time
                    # 构建插入语句
                    insert_stmt = (
                        "INSERT INTO `{table}` ({fields},ship_region_type) "
                        "VALUES ({values},{ship_region_type})"
                    ).format(
                        table=table_name,
                        fields=fields,
                        values=values,
                        ship_region_type=ship_region_type
                    )
                    # 执行sql语句插入数据，record.values()用于获取字段中的值
                    cursor.execute(insert_stmt, tuple(record.values()))
            connection.commit()
            print('插入完成!')
    except Exception as e:
        print(f'insert fail::{e}')
        connection.rollback()
        pass

try:
    # 海船初查
    # insertData('1', '0')
    # 海船复查
    # insertData('1', '1')
    # # 内河船初查
    # insertData('0', '0')
    # 内河船复查
    insertData('0', '1')
finally:
    # 关闭数据库连接
    connection.close()
